<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match="/">
using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Reflection;
using System.Configuration;

namespace <xsl:value-of select="Root/@NameSpace" />
{
	/// <summary>
	/// 数据访问层基类
	/// </summary>
	public class DataAccessBase
	{
		protected SqlParameter paramReturnValue;
		protected SqlCommand sqlComm;

		public int ReturnValue
		{
			get
			{
				return (System.Int32)this.paramReturnValue.Value;
			}
		}

		/// <summary>
		/// 初始化SqlCommand命令
		/// </summary>
		/// <param name="sSPName">要初始化的存储过程名称</param>
		protected void InitCommand(string sSPName)
		{
			this.sqlComm = new SqlCommand();
			this.sqlComm.CommandText = sSPName;
			this.sqlComm.CommandType = CommandType.StoredProcedure;
			//--------------------------------------------------------
			this.paramReturnValue = new SqlParameter();
			this.paramReturnValue.ParameterName = "@ReturnValue";
			this.paramReturnValue.SqlDbType = SqlDbType.Int;
			this.paramReturnValue.Size = 4;
			this.paramReturnValue.Direction = ParameterDirection.ReturnValue;
			this.sqlComm.Parameters.Add(this.paramReturnValue);
			//--------------------------------------------------------
		}

		/// <summary>
		/// 执行查询命令，并返回DataTable对象
		/// </summary>
		/// <returns>执行结果</returns>
		public DataTable ExecuteDataTable()
		{
			return ExecuteDataTable("");
		}

		/// <summary>
		/// 执行查询命令，并返回DataTable对象
		/// </summary>
		/// <param name="sTableName">返回的DataTable名称</param>
		/// <returns>执行结果</returns>
		public DataTable ExecuteDataTable(string sTableName)
		{
			SqlConnection sqlConn = new SqlConnection(GetConnectionString());
			sqlComm.Connection = sqlConn;
			SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlComm);
			
			DataTable dtResult = new DataTable();

			try
			{
				sqlAdapter.Fill(dtResult);
			}
			catch(Exception ex)
			{
				throw new DataAccessException(ex.Message,ex);
			}
			finally
			{
				// 确保数据库连接关闭
				if(sqlConn.State != ConnectionState.Closed)
				{
					sqlConn.Close();
				}	
			}
			if(sTableName != "")
			{
				dtResult.TableName = sTableName;
			}
			return dtResult;
		}

		/// <summary>
		/// 执行查询命令，并返回DataSet对象
		/// </summary>
		/// <returns>执行结果</returns>
		public DataSet ExecuteDataSet()
		{
			DataTable dtResult = ExecuteDataTable();
			DataSet dsReturn = new DataSet();
			dsReturn.Tables.Add(dtResult);
			return dsReturn;
		}

		/// <summary>
		/// 执行查询命令，并返回DataSet对象
		/// </summary>
		/// <param name="sTableName">返回的DataTable名称</param>
		/// <returns>执行结果</returns>
		public DataSet ExecuteDataSet(string sTableName)
		{
			DataTable dtResult = ExecuteDataTable(sTableName);
			DataSet dsReturn = new DataSet();
			dsReturn.Tables.Add(dtResult);
			return dsReturn;
		}

		/// <summary>
		/// 执行查询命令
		/// </summary>
		/// <returns>影响记录数</returns>
		public int ExecuteNoQuery()
		{
			int iResult = -1;
			SqlConnection sqlConn = new SqlConnection(GetConnectionString());
			try
			{
				sqlConn.Open();
				sqlComm.Connection = sqlConn;
				iResult = sqlComm.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				throw new DataAccessException(ex.Message,ex);
			}
			finally
			{
				// 确保数据库连接关闭
				if(sqlConn.State != ConnectionState.Closed)
				{
					sqlConn.Close();
				}	
			}
			return iResult;
		}

		/// <summary>
		/// 执行查询命令，并返回XmlDocument对象
		/// </summary>
		/// <returns>执行结果</returns>
		public XmlDocument ExecuteXmlDom()
		{
			return ExecuteXmlDom("ROOT");
		}

		/// <summary>
		/// 执行查询命令，并返回XmlDocument对象
		/// </summary>
		/// <param name="sRootName">根节点名称</param>
		/// <returns>执行结果</returns>
		public XmlDocument ExecuteXmlDom(string sRootName)
		{
			string sXml = "";
			SqlConnection sqlConn = new SqlConnection(GetConnectionString());
			sqlComm.Connection = sqlConn;
			
			try
			{
				sqlConn.Open();
				XmlReader reader = sqlComm.ExecuteXmlReader();

				while(reader.ReadState != ReadState.EndOfFile)
				{
					reader.MoveToContent();
					sXml += reader.ReadOuterXml();
				}
			}
			catch(Exception ex)
			{
				throw new DataAccessException(ex.Message,ex);
			}
			finally
			{
				// 确保数据库连接关闭
				if(sqlConn.State != ConnectionState.Closed)
				{
					sqlConn.Close();
				}	
			}

			XmlDocument xmlDocument = new XmlDocument();
			<xsl:text disable-output-escaping="yes">
			xmlDocument.LoadXml("&lt;" + sRootName + "&gt;" + sXml + "&lt;/" + sRootName + "&gt;");
			</xsl:text>
			return xmlDocument;
		}

		/// <summary>
		/// 存储过程接受参数赋值。
		/// </summary>
		/// <param name="dtParameter">包含参数信息的 DataTable</param>
		public void ReceiveParameter(DataTable dtParameter)
		{
			// 枚举命令对象的参数集合
			foreach(SqlParameter param in sqlComm.Parameters)
			{
				string sParamName = param.ParameterName;

				// 去掉参数名的前缀"@"
				string sColumnName = sParamName.Substring(1,sParamName.Length-1);

				// 通过枚举参数化表格的列信息，如果找到统一名称的列
				// 就把该列的值赋給参数的值
				foreach(DataColumn column in dtParameter.Columns)
				{
					if(sColumnName == column.ColumnName)
					{
						param.Value = dtParameter.Rows[0][sColumnName];
						break;
					}
				}
			}
		}

		/// <summary>
		/// 存储过程接受参数赋值。
		/// </summary>
		/// <param name="objParameter">包含参数信息的实体对象</param>
		public void ReceiveParameter(object objEntity)
		{
			// 得到该实体对象的具体类型信息
			Type objType = objEntity.GetType();

			// 枚举命令对象的参数集合
			foreach(SqlParameter param in sqlComm.Parameters)
			{
				string sParamName = param.ParameterName;
				// 去掉参数名的前缀"@"
				string sColumnName = sParamName.Substring(1,sParamName.Length - 1);

				// 从实体对象中去检索是否具有相同名称的公有属性
				PropertyInfo property = objType.GetProperty(sColumnName);
				if(property != null)
				{
					param.Value = property.GetValue(objEntity,null);
				}
			}
		}

		/// <summary>
		/// 获取数据库连接串
		/// </summary>
		private string GetConnectionString()
		{
			string sConnString = ConfigurationSettings.AppSettings["<xsl:value-of select="Root/@DBName" />"];
			return sConnString;
		}
	}
}
</xsl:template>
</xsl:stylesheet>